package controllers

import (
    "database/sql"
    "fmt"
    "github.com/astaxie/beego"
    _ "github.com/go-sql-driver/mysql" //
    "os"
)

func init() {
	mySQLAddr = os.Getenv("MYSQLADDR")
	mySQLPwd = os.Getenv("MYSQLPWD")
	//mySQLAddr = "127.0.0.1"
	//mySQLPwd = "root"
}

var mySQLAddr string
var mySQLPwd string

// MainController .
type MainController struct {
	beego.Controller
}

// Get .
func (c *MainController) Get() {

    connString := "root:" + mySQLPwd + "@tcp(" + mySQLAddr + ":3306)/mysql?charset=utf8"

    db, err := sql.Open("mysql", connString)

    defer db.Close()
    
    if err != nil {
        fmt.Printf("Can't connect to mysql! - %v\n", err)
        c.Ctx.WriteString("Can't connect to mysql!")
        return
    }

    // 查询MySQL系统变量

    strVersion := c.querySystemVariable(db, "show variables like 'version'")
    strComment := c.querySystemVariable(db, "show variables like 'version_comment'")
    strOS := c.querySystemVariable(db, "show variables like 'version_compile_os'")
    strUptime := c.querySystemVariable(db, "show status like 'Uptime'")
    strReceived := c.querySystemVariable(db, "show status like 'Bytes_received'")
    strSend := c.querySystemVariable(db, "show status like 'Bytes_sent'")

    html := "<html><head><style>body{font-family:'verdana';h4{font-weight:bold;}}</style></head><body>"

    html = html + "<h4>已连接到MySQL(Golang):</h4>" + strVersion + "<br>" + 
        strOS + "<br>" + strComment + "<br>" + strUptime + "s<br>" + strReceived + "<br>" + strSend + "<br>"

    // 查询默认创建的表 "template"

    rs := c.queryTemplate(db, "SELECT * FROM template LIMIT 10")

    if len(rs) > 0 {

        strTable := "<br><table border=\"1\"><tr><th>id</th><th>language</th><th>framework</th><th>create_time</th></tr>"
        for _, v := range rs {
            tr := "<tr><th>" + v.id + "</th><th>" + v.language + "</th><th>" + v.framework + "</th><th>" + v.create_time + "</th></tr>"
            strTable = strTable + tr
        }
        html = html + strTable + "</table></body></html>"

    } else {
        html = html + "</body></html>"
    }

    c.Ctx.WriteString(html)
}

// 查询MySQL系统变量
//
func (c *MainController) querySystemVariable(db *sql.DB, sql string) (strResult string) {

    rows, err := db.Query(sql)

    if err != nil {
        fmt.Printf("Query NG! - %v\n", err)
        return
    }

    columns, _ := rows.Columns()
    scanArgs := make([]interface{}, len(columns))
    values := make([]interface{}, len(columns))

    for i := range values {
        scanArgs[i] = &values[i]
    }

    for rows.Next() {
        err = rows.Scan(scanArgs...)
        record :=  make(map[string]string)
        for i, col := range values {            
            if col != nil {
                record[columns[i]] = string(col.([]byte))
            }
        }
        strResult = fmt.Sprintf("%s: %s", record["Variable_name"], record["Value"])
        break
    }

    return
}

// 查询默认创建的表 "template"
//
func (c *MainController) queryTemplate(db *sql.DB, sql string) (strResults []templateRecord) {

    db.Exec("USE template")

    rows, err := db.Query(sql)

    if err != nil {
        fmt.Printf("Query NG! - %v\n", err)
        return
    }

    columns, _ := rows.Columns()
    scanArgs := make([]interface{}, len(columns))
    values := make([]interface{}, len(columns))

    for i := range values {
        scanArgs[i] = &values[i]
    }

    for rows.Next() {
        err = rows.Scan(scanArgs...)
        record :=  make(map[string]string)
        for i, col := range values {
            if col != nil {
                record[columns[i]] = string(col.([]byte))
            }
        }
        
        tr := templateRecord{id: record["id"], language: record["language"], 
            framework: record["framework"], create_time: record["create_time"]}
        
        strResults = append(strResults, tr)
    }

    return
}

type templateRecord struct {
    id string
    language string
    framework string
    create_time string
}
